DPG: A Cache-Efficient Accelerator for Sorting and for Join Operators

Sorting and join are the heart of most database operations. For main memory databases, the two operations are memory bounded not CPU. Especially the common step in both of them, record retrieval, causes a lot of random memory accesses. We present a new algorithm for fast record retrieval, distributeprobe-gather, or DPG. DPG is a cache conscious two pass algorithm for the main memory record retrieval problem. DPG has important applications both in sorting and in joins. Current main memory sorting algorithms split their work into three phases: extraction of key-pointer pairs; sorting of the key-pointer pairs; and copying of the original records into the destination array according the sorted key-pointer pairs. The last phase is essentially record retrieval and it dominates today’s sorting time. Hence, the use of DPG in the third phase accelerates the speeds of the existing sorting algorithms. DPG also provides two new join methods for foreign key joins, DPG-move join and DPG-sort join. DPG algorithm is applied twice in the new join methods to overcome the memory bottleneck: batch lookup in B+ tree indexes to construct the join triples and join two relations with join triples. The resulting join methods with DPG are faster than other join methods that do not use the index on the foreign key, because DPG join is cache efficient and at the same time DPG join eliminates the time for sorting or for hashing. The ideas presented for foreign key join can also be extended to faster record pair retrieval for spatial and temporal databases. According to our experimental results, when DPG is applied: the existing sorting algorithms can be improved 30% and our new DPG join algorithms is up to 2 times faster than the existing join algorithms.